package com.oa.tools;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.oa.tools.entity.OrgElementWithPerson;

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

public interface TkMapper<T> extends Mapper<T>, MySqlMapper<T> {
	
	List<OrgElementWithPerson> selectOrgWithPerson();
	
	OrgElementWithPerson selectOneOrgWithPerson(@Param(value = "mobile") String mobile);
	
	@Select(value = {"select t.fd_name 流程模板名称, \r\n" + 
			" decode(m.doc_status,'30','发布','20','待审','10','草稿','11','驳回','00','废弃') as 审批状态,"+
			"    m.fd_number 流程编号 , \r\n" + 
			"    e.fd_name 申请人, \r\n" + 
			"    e1.fd_name 申请部门, \r\n" + 
			"    la.fd_fact_node_name 节点名称  ,\r\n" + 
			"    e2.fd_name 操作者,\r\n" + 
			"    e3.fd_name 操作人部门,\r\n" + 
			"    --m.fd_id,\r\n" + 
			"    decode(la.fd_action_key,'handler_pass','通过','drafter_submit','提交','handler_commission','转办','admin_jump','特权跳过', \r\n" + 
			"    'handler_refuse','驳回','repeat_jump','重复跳过','drafter_return','起草人撤回','drafter_abandon','起草人废弃', \r\n" + 
			"    '_pocess_end','流程结束') as 操作类型, \r\n" + 
			"    to_char(la.fd_create_time,'yyyy-MM-dd ') 操作日期 , \r\n" + 
			"    to_char(la.fd_create_time,'HH24:mi:ss') 操作时间, \r\n" + 
			"    la.fd_audit_note as 处理意见 \r\n" + 
			"    from km_review_main m  \r\n" + 
			"   left join lbpm_audit_note la on la.fd_process_id = m.fd_id \r\n" + 
			"    --left join sys_audit_log l on l.fd_model_id = m.fd_id and l.fd_operator_id = fd_handler_id\r\n" + 
			"    left join sys_org_element e2 on e2.fd_id = la.fd_handler_id\r\n" + 
			"    left join sys_org_element e3 on e3.fd_id = e2.fd_parentid\r\n" + 
			"    left join sys_org_element e on e.fd_id = m.doc_creator_id \r\n" + 
			"    left join sys_org_element e1 on e1.fd_id = e.fd_parentid \r\n" + 
			"    left join km_review_template t on t.fd_id = m.fd_template_id \r\n" + 
			"    where m.doc_status <> '10' and m.doc_status <> '00' and la.fd_action_key not like 'admin%' and la.fd_action_key <> 'repeat_jump' and e1.fd_name like '%网约车%' \r\n" + 
			"    order by m.fd_number,la.fd_create_time" })
	List<Map<String,Object>> selectWYCFlowAuditBySql();
	
	@Select(value = {"select t.fd_name 流程模板名称,  \r\n" + 
			"     m.fd_number 流程编号 ,  \r\n" + 
			" decode(m.doc_status,'30','发布','20','待审','10','草稿','11','驳回','00','废弃') as 审批状态,"+
			"     e.fd_name 申请人,  \r\n" + 
			"     e1.fd_name 申请部门,  \r\n" + 
			"     la.fd_fact_node_name 节点名称 , \r\n" + 
			"      e2.fd_name 操作者,\r\n" + 
			"      e3.fd_name 操作人部门,  \r\n" + 
			"     decode(la.fd_action_key,'handler_pass','通过','drafter_submit','提交','handler_commission','转办','admin_jump','特权跳过',  \r\n" + 
			"     'handler_refuse','驳回','repeat_jump','重复跳过','drafter_return','起草人撤回','drafter_abandon','起草人废弃',  \r\n" + 
			"     '_pocess_end','流程结束') as 操作类型,  \r\n" + 
			"     to_char(la.fd_create_time,'yyyy-MM-dd ') 操作日期 ,  \r\n" + 
			"     to_char(la.fd_create_time,'HH24:mi:ss') 操作时间,  \r\n" + 
			"     la.fd_audit_note as 处理意见  \r\n" + 
			"     from fs_expense_main m   \r\n" + 
			"     left join lbpm_process lp on  lp.fd_model_id = m.fd_id  \r\n" + 
			"    -- left join sys_audit_log l on l.fd_model_id = m.fd_id  \r\n" + 
			"     left join lbpm_audit_note la on la.fd_process_id = m.fd_id  \r\n" + 
			"     left join sys_org_element e2  on e2.fd_id = la.fd_handler_id \r\n" + 
			"     left join sys_org_element e3  on e3.fd_id = e2.fd_parentid \r\n" + 
			"     left join sys_org_element e on e.fd_id = m.doc_creator_id  \r\n" + 
			"     left join sys_org_element e1 on e1.fd_id = e.fd_parentid  \r\n" + 
			"     left join fs_expense_category t on t.fd_id = m.doc_category_id  \r\n" + 
			"     where  m.doc_status <> '00' and m.doc_status <> '10' and e1.fd_name like '%网约车%' and la.fd_action_key <> 'repeat_jump'  and la.fd_action_key not like 'admin%'  \r\n" + 
			"     order by m.fd_number,la.fd_create_time" })
			List<Map<String,Object>> selectWYCFeeAuditBySql();
	
	@Select(value = { "select \r\n" + 
			"m.doc_subject as 报销名称,\r\n" + 
			"m.fd_number as 单据编号,\r\n" + 
			" e1.fd_name as 报销人, \r\n" + 
			"  e2.fd_name as 费用归属部门,\r\n" + 
			"   to_char(m.doc_create_time, 'YYYY-MM-DD') as 报销日期,\r\n" + 
			"   a.fd_bank_account 收款人帐号,\r\n" + 
			"   a.fd_account_name 收款人名称, \r\n" + 
			"   a.fd_payment 金额,\r\n" + 
			"    m.fd_pay_time as 财务付款日期,\r\n" + 
			"    m.doc_publish_time as 流程结束日期,\r\n" + 
			"  decode(m.doc_status,'30','发布','20','待审','10','草稿','11','驳回','00','废弃') as 审批状态 \r\n" + 
			"    from fs_expense_main m\r\n" + 
			"left join fs_expense_accounts a on m.fd_id = a.fd_cost_apply_id \r\n" + 
			" left join sys_org_element e1 on e1.fd_id = m.fd_claimant_id           --申请人\r\n" + 
			" left join sys_org_element e2 on m.fd_claimant_dept_id = e2.fd_id      --申请部门\r\n" + 
			" left join fs_expense_category fc on fc.fd_id = m.doc_category_id\r\n" + 
			"where fc.fd_name like '%'|| #{fdName} ||'%'"
			 })
	List<Map<String,Object>> selectPublishedFee(@Param("fdName") String fdName);
	
	@Select(value = { "select distinct * from(                             --去掉一人多次审批导致的重复\r\n" + 
			"     select  \r\n" + 
			"    to_char(m.doc_create_time, 'YYYY-MM-DD') as 报销日期, \r\n" + 
			"      m.fd_number as 单据编号, \r\n" + 
			"      e1.fd_name as 报销人, \r\n" + 
			"      e2.fd_name as 费用归属部门, \r\n" + 
			"      i.fd_name as 费用项目, \r\n" + 
			"    d.fd_orgin_total_money as 金额, \r\n" + 
			"      d.fd_use as 用途摘要,     \r\n" + 
			"       decode(m.doc_status,'30','发布','20','待审','10','草稿','11','驳回','00','废弃') as 审批状态\r\n" + 
			"       from fs_expense_main m \r\n" + 
			"       left join fs_expense_project_detail d on m.fd_id = d.fd_expense_main_id --报销明细表\r\n" + 
			"       left join  fs_base_expense_item  i on d.fd_expense_item_id = i.fd_id    --费用类型表\r\n" + 
			"       left join sys_org_element e1 on e1.fd_id = m.fd_claimant_id           --申请人\r\n" + 
			"       left join sys_org_element e2 on m.fd_claimant_dept_id = e2.fd_id      --申请部门\r\n" + 
			"       where m.doc_status ='30'                    -- 已发布\r\n" + 
			"       ) t   " })
	List<Map<String,Object>> selectFinishedExpenseBySql();
	
	
	@Select(value = { "select distinct * from(                            \r\n" + // --去掉一人多次审批导致的重复
			"select \r\n" + 
			" m.fd_number as 单据编号,\r\n" + 
			" e1.fd_name as 申请人,\r\n" + 
			" e2.fd_name as 申请部门,\r\n" + 
			" m.fd_pay_time as 付款日期,\r\n" + 
			" to_char(m.doc_create_time, 'YYYY-MM-DD') as 申请日期,\r\n" + 
			" to_char(m.fd_accounting_date, 'YYYY-MM') as 归属期间,\r\n" + 
			" m.doc_content as 报销内容,\r\n" + 
			" i.fd_name as 费用类型,\r\n" + 
			" d.fd_use as 用途摘要,\r\n" + 
			" to_char(d.fd_begin_date, 'YYYY-MM-DD') as 费用开始日期,\r\n" + 
			" to_char(d.fd_end_date, 'YYYY-MM-DD') as 费用结束日期,\r\n" + 
			" d.fd_orgin_total_money as 本币金额,\r\n" + 
			"  d.fd_approved_apply_money as 核准申报金额,\r\n" + 
			"  decode(m.doc_status,'30','发布','20','待审','10','草稿','11','驳回','00','废弃') as 审批状态,\r\n" + 
			"  e.fd_name as 处理人\r\n" + 
			"  from fs_expense_main m\r\n" + 
			"  left join fs_expense_project_detail d on m.fd_id = d.fd_expense_main_id \r\n" + //--报销明细表
			"  left join  fs_base_expense_item  i on d.fd_expense_item_id = i.fd_id    \r\n" + //--费用类型表
			"  left join lbpm_process lp on m.fd_id = lp.fd_model_id                   \r\n" + //-- 流程实例
			"  left join lbpm_history_workitem le on le.fd_process_id = lp.fd_id       \r\n" + //-- 历史处理人
			"  left join sys_org_element e on e.fd_id = le.fd_handler_id\r\n" + 
			"  left join sys_org_element e1 on e1.fd_id = m.fd_claimant_id           \r\n" + //--申请人
			"  left join sys_org_element e2 on m.fd_claimant_dept_id = e2.fd_id      \r\n" + //--申请部门
			"  where e.fd_name  in('赵茜','穆秋月','杨孟菲','王三洋')                       \r\n" + //-- 运营支持岗已处理
			"  and m.doc_status <> '10'  and m.doc_status <> '00'                    \r\n" + //-- 排除 草稿和废弃 
			"  ) t \r\n" + 
			"\r\n" + 
			"" })
	List<Map<String,Object>> selectWYCExpenseBySql();
	
	
	@Select(value = { "select \r\n" + 
			"	regexp_substr(a.fd_bank,'[^-]+',1,2) 业务参考号, \r\n" + 
			"	a.fd_bank_account 收款人帐号, \r\n" + 
			"	a.fd_account_name 收款人名称, \r\n" + 
			"	regexp_substr(a.fd_bank,'[^-]+',1,1) 收方开户支行, \r\n" + 
			"	regexp_substr(a.fd_bank,'[^-]+',1,3) 收款人所在省, \r\n" + 
			"	regexp_substr(a.fd_bank,'[^-]+',1,4) 收款人所在市, \r\n" + 
			"	'' 收方邮件地址, \r\n" + 
			"	'' 收方移动电话, \r\n" + 
			"	'' 币种, \r\n" + 
			"	'' 付款分行, \r\n" + 
			"	'' 结算方式, \r\n" + 
			"	'' 业务种类, \r\n" + 
			"	'' 付方帐号, \r\n" + 
			"	'' 付方虚拟户编号, \r\n" + 
			"	'' 期望日, \r\n" + 
			"	'' 期望时间, \r\n" + 
			"	regexp_substr(m.fd_table_head,'[^(报销)]+',1,1)||'费' 用途, \r\n" + 
			"	a.fd_payment 金额, \r\n" + 
			"	regexp_substr(a.fd_bank,'[^-]+',1,2) 收方联行号, \r\n" + 
			"	regexp_substr(a.fd_bank,'[^-]+',1,5) 收方开户银行, \r\n" + 
			"	'' 业务摘要, \r\n" + 
			"	p.fd_name 发票备注 \r\n" + 
			"  ,m.fd_number OA报销单号 \r\n" + 
			"	from fs_expense_main m \r\n" + 
			"	left join  fs_expense_accounts a on m.fd_id = a.fd_cost_apply_id \r\n" + 
			"	left join fs_expense_category c on m.doc_category_id  = c.fd_id\r\n" + 
			"	left join fs_base_project_accounting p on m.fd_project_id = p.fd_id \r\n"+
			"	where m.fd_payment_status =1 \r\n" + 
			"	 and m.doc_status = '20' \r\n"+
			"	 and c.fd_name in ('停车费报销','拖车费报销','新车加油费','洗车费报销','保养/维修费报销','理赔款退还','订金退还','租金退还')"})
	List<Map<String,Object>> selectCMBPayInfoBySql();
	
	@Select(value = { "select \r\n" + 
	"  'D' 转账类型,\r\n" + 
	"  '03002852658' 付款单位账号,\r\n" + 
	"  a.fd_bank_account 收款单位账号, \r\n" + 
	"  a.fd_account_name 收款单位名称, \r\n" + 
	"  regexp_substr(a.fd_bank,'[^-]+',1,5) 收款单位开户行名,\r\n" + 
	"  regexp_substr(a.fd_bank,'[^-]+',1,1) 收款单位网点名称, \r\n" + 
	"  regexp_substr(a.fd_bank,'[^-]+',1,2) 收款银行行号, \r\n" + 
	"  a.fd_payment 付款金额, \r\n" + 
	"  regexp_substr(m.fd_table_head,'[^(报销)]+',1,1)||'费' 付款用途, \r\n" + 
	"  m.fd_table_head,\r\n" + 
	"  '' 加急标志 \r\n" + 
	"  ,m.fd_number OA报销单号 \r\n" + 
	"  from fs_expense_main m \r\n" + 
	"  left join  fs_expense_accounts a on m.fd_id = a.fd_cost_apply_id \r\n" + 
	"  left join fs_expense_category c on m.doc_category_id  = c.fd_id\r\n" + 
	"  where m.fd_payment_status = 1  \r\n" + 
	"	 and m.doc_status = '20' \r\n"+
	"  and c.fd_name not in ('停车费报销','拖车费报销','新车加油费','洗车费报销','保养/维修费报销','理赔款退还','订金退还','租金退还')"})
	List<Map<String,Object>> selectShangHaiPayInfoBySql();
	
	@Select(value = { "select distinct * from(                              --去掉一人多次审批导致的重复\r\n" + 
			"select  \r\n" + 
			"m.fd_number as 单据编号, \r\n" + 
			"e1.fd_name as 申请人, \r\n" + 
			"e2.fd_name as 申请部门, \r\n" + 
			"to_char(m.doc_create_time, 'YYYY-MM-DD') as 申请日期, \r\n" + 
			"to_char(m.fd_accounting_date, 'YYYY-MM') as 归属期间, \r\n" + 
			"m.doc_content as 报销内容, \r\n" + 
			"i.fd_name as 费用类型, \r\n" + 
			"d.fd_use as 用途摘要, \r\n" + 
			"to_char(d.fd_begin_date, 'YYYY-MM-DD') as 费用开始日期, \r\n" + 
			"to_char(d.fd_end_date, 'YYYY-MM-DD') as 费用结束日期, \r\n" + 
			"d.fd_orgin_total_money as 本币金额, \r\n" + 
			" d.fd_approved_apply_money as 核准申报金额, \r\n" + 
			" decode(m.fd_payment_status,'2','已付款','未付款') as 付款状态, \r\n" + 
			" e.fd_name as 处理人 \r\n" + 
			" from fs_expense_main m \r\n" + 
			" left join fs_expense_project_detail d on m.fd_id = d.fd_expense_main_id  --报销明细表\r\n" + 
			" left join  fs_base_expense_item  i on d.fd_expense_item_id = i.fd_id     --费用类型表\r\n" + 
			" left join lbpm_process lp on m.fd_id = lp.fd_model_id                    -- 流程实例\r\n" + 
			" left join lbpm_history_workitem le on le.fd_process_id = lp.fd_id        -- 历史处理人\r\n" + 
			" left join sys_org_element e on e.fd_id = le.fd_handler_id \r\n" + 
			" left join sys_org_element e1 on e1.fd_id = m.fd_claimant_id            --申请人\r\n" + 
			" left join sys_org_element e2 on m.fd_claimant_dept_id = e2.fd_id       --申请部门\r\n" + 
			" where  m.doc_status <> '10'  and m.doc_status <> '00'                     -- 排除 草稿和废弃 \r\n" + 
			" and i.fd_name like '%'|| #{feeType}||'%' \r\n" + 
			") t\r\n" + 
			" order by t.付款状态  " })
	List<Map<String,Object>> selectTuanjianPayInfoBySql(@Param("feeType") String feeType);
	
	@Select(value = { "select \r\n" + 
			"m.fd_number 报销单号,\r\n" + 
			"e.fd_name 报销部门,\r\n" + 
			"t.fd_vin 车架号,\r\n" + 
			"t.fd_fee_type 费用科目,\r\n" + 
			"t.fd_vehicle 车型,\r\n" + 
			"t.fd_fee 费用,\r\n" + 
			"t.fd_comment 备注\r\n" + 
			"from EKP_VEHICLE t \r\n" + 
			"left join fs_expense_main m on t.fd_parent_id = m.fd_id \r\n" + 
			"left join sys_org_element e on e.fd_id = m.fd_claimant_dept_id\r\n" + 
			" where t.fd_fee_type = '新车加油费' and m.doc_status <> '00' "})
	List<Map<String,Object>> selectFuelChargingListBySql();
	
	
	@Select(value = { "select \r\n" + 
			"m.fd_number 报销单号,\r\n" + 
			"e.fd_name 报销部门,\r\n" + 
			"t.fd_vin 车架号,\r\n" + 
			"t.fd_fee_type 费用科目,\r\n" + 
			"t.fd_vehicle 车型,\r\n" + 
			"t.fd_fee 费用,\r\n" + 
			"t.fd_comment 备注\r\n" + 
			"from EKP_VEHICLE_CLEANING t \r\n" + 
			"left join fs_expense_main m on t.fd_parent_id = m.fd_id \r\n" + 
			"left join sys_org_element e on e.fd_id = m.fd_claimant_dept_id\r\n" + 
			" where t.fd_fee_type = '洗车费' and m.doc_status <> '00' "})
	List<Map<String,Object>> selectVehicleCleaingListBySql();
	
	
	@Select(value = { "select m.fd_key fileKey,\r\n" + 
			"       m.fd_file_name fileName,\r\n" + 
			"       f.fd_file_path fileLocalPath\r\n" + 
			"  from sys_att_main m\r\n" + 
			"  left join sys_att_file f\r\n" + 
			"    on f.fd_id = m.fd_file_id\r\n" + 
			" where m.fd_id = #{fdId} "})
	Map<String,String> selectOAFileById(@Param("fdId") String fdId);
	
}
